use ArbolDeVoces

if not exists (select 1 from sys.objects where object_id = object_id('VocesConNivel'))
begin

create table VocesConNivel
(
  	[idvoz] [int] NULL,
	[descripcion] [varchar](1000) NULL,
	[nivel] [int] NULL,
	[idvozpadre] [int] NULL
) ON [PRIMARY]

end

GO

IF OBJECT_ID (N'dbo.GetNivelVoz', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetNivelVoz;
GO
CREATE FUNCTION dbo.GetNivelVoz (@VozId int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    declare @currentVozId int
    declare @vozIdFromSelect int
    declare @nivel int
    
    set @nivel = 1
    set @currentVozId = @VozId
    
    while @currentVozId is not null
    begin
       set @vozIdFromSelect = null            
       
       select @vozIdFromSelect = idvozp
       from tjerarquia
       where idvozh = @currentVozId         
       
       set @currentVozId = @vozIdFromSelect           
       
       if @vozIdFromSelect is not null
       begin        
		set @nivel = @nivel + 1
       end                   
                
    end   
    
    return(@nivel)
        
END
GO

delete from VocesConNivel

go

insert into VocesConNivel (idvoz, descripcion, nivel, idvozpadre)
select 
v.IdVoz,
v.Descripcion,
dbo.GetNivelVoz(v.IdVoz),
(select idvozp from tJerarquia j where j.IdVozH = v.IdVoz) as idvozpadre
from tVoces v

--select * from VocesConNivel








